package com.DBUtil;

import java.sql.Blob;
import java.sql.Connection;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;


import com.DBUtil.DbHelper;


public class DBTool {
	private static String defaultDbType = "oracle";

	public static String extGetSqlText(String sqlcode, Map sqlparam) {
		return sqlcode;
	
	}

	/**
	 * 创建分页查询的sql语句
	 * 
	 * @param sqlStr
	 *            原始语句
	 * @param currentpage
	 *            当前页码
	 * @param pagesize
	 *            页尺寸
	 * @return
	 */
	private static String creatPageSql(String sqlStr, int currentpage,
			int pagesize) {
		String tmpStr = "", sqlTmpStr = "";
		int pos, orderByPos;
		if (currentpage < 1 || pagesize < 1)
			return "-1";
		sqlTmpStr = sqlStr.trim().toLowerCase();
		tmpStr = sqlTmpStr.substring(0, 6);
		// 如果不是select打头
		if (!tmpStr.equals("select"))
			return "-1";
		orderByPos = sqlTmpStr.indexOf("order by");
		// 不能用sort_num这个做字段名
		sqlTmpStr = sqlTmpStr.substring(7);
		pos = sqlTmpStr.indexOf("sort_num");
		if (pos > 0)
			return "-1";

		pos = sqlTmpStr.indexOf(" from ");
		if (pos <= 1)
			return "-1";

		sqlTmpStr = sqlTmpStr.trim();
		tmpStr = sqlTmpStr.substring(0, 1);

		if (tmpStr.equals("*"))
			return "-1";

		tmpStr = "rownum";
		if (orderByPos > 1)
			tmpStr = "row_number() over(" + sqlStr.substring(orderByPos) + ")";

		pos = (currentpage - 1) * pagesize + 1;
		String lsstr=sqlStr.toLowerCase();
		int disPos=lsstr.indexOf("distinct");
		//System.out.println("sqlStr:"+lsstr);
		//System.out.println("dispos:"+disPos);
		sqlTmpStr = sqlStr.substring(7);
		if(disPos>0){//如果sql语句包含distinct字符
			//System.out.println("distinct has!");
			String distinctTmpStr="";
			if(orderByPos>1)
				distinctTmpStr=sqlStr.substring(0,orderByPos-1);
			else
				distinctTmpStr=sqlStr;
			
			sqlTmpStr = "select * from (select " + tmpStr + " as sort_num, t.* from ("
					+ distinctTmpStr + ") t ) where sort_num >= " + String.format("%d", pos)
					+ " and sort_num <=" + String.format("%d", pos + pagesize - 1);
			
		}
		else{
		sqlTmpStr = "select * from (select " + tmpStr + " as sort_num, "
				+ sqlTmpStr + ") where sort_num >= " + String.format("%d", pos)
				+ " and sort_num <=" + String.format("%d", pos + pagesize - 1);
		}
		return sqlTmpStr;
	}

	public static Map query(String sql, Object... args) {
		return queryEx(defaultDbType, sql, args);
	}
	/**
	 * 获取oralce中Clob类型数据
	 * @param sql
	 * @param fieldname
	 * @param args
	 * @return
	 */
    public static String queryClob(String sql,String fieldname,Object... args){
    	String ret="";
    	List<Object> a = new ArrayList<Object>();
		for (int i = 0; i < args.length; i++) {
			a.add(args[i]);
		}
    	DbHelper dh = new DbHelper();
		try {
    	Map o = dh.query4Map(sql, a.toArray());
    	if (o != null) {
    		oracle.sql.CLOB   clobtmp   =   (oracle.sql.CLOB)o.get(fieldname);
    	 		ret=clobtmp.getSubString((long)1,(int)clobtmp.length());   
    	}
    	dh.close();
		} catch (Exception e) {
			dh.close();
		}
		return ret;
    }
    /**
     * 查询单条记录
     * @param sql
     * @param fieldname
     * @param args
     * @return
     */
    public static Map query4Map(String sql,Object... args){
    	Map ret=new HashMap<String, Object>();
    	List<Object> a = new ArrayList<Object>();
		for (int i = 0; i < args.length; i++) {
			a.add(args[i]);
		}
    	DbHelper dh = new DbHelper();
		try {
			ret = dh.query4Map(sql, a.toArray());
    	  	dh.close();
		} catch (Exception e) {
			dh.close();
		}
		return ret;
    }
    
    /**
	 * 将查询出oralce中Clob类型数据转换成字符串格式并返回查询出的数据
	 * @param sql
	 * @param fieldname
	 * @param args
	 * @return 返回的是单条记录。
	 */
    public static Map queryClobMap(String sql,String fieldname,Object... args){
    	Map ret=new HashMap<String, Object>();
    	List<Object> a = new ArrayList<Object>();
		for (int i = 0; i < args.length; i++) {
			a.add(args[i]);
		}
    	DbHelper dh = new DbHelper();
		try {
			ret = dh.query4Map(sql, a.toArray());
    	String clobfield="";
    	if (ret != null) {
    		oracle.sql.CLOB   clobtmp   =   (oracle.sql.CLOB)ret.get(fieldname);
    		clobfield=clobtmp.getSubString((long)1,(int)clobtmp.length());   
    	}
    	ret.put(fieldname,clobfield);
    	dh.close();
		} catch (Exception e) {
			dh.close();
		}
		return ret;
    }
	/**
	 * 根据sql和参数查询记录，并返回查询结果。 返回值为：dataObj：返回查询数据 retMsg：返回信息 retCode:返回码
	 * retCount：返回记录数
	 * 
	 * @param sql
	 * @param args
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static Map queryEx(String dbtype, String sql, Object... args) {
		List<Object> a = new ArrayList<Object>();
		for (int i = 0; i < args.length; i++) {
			a.add(args[i]);
		}
		Map result = new HashMap();
		DbHelper dh = new DbHelper(dbtype);
		try {

			ArrayList ulist = dh.queryRecords4Map(sql, a.toArray());
			if (ulist != null) {
				result.put("dataObj", ulist);
				result.put("retCount", ulist.size());
				result.put("retMsg", "ok");
				result.put("retCode", 1);

			} else {
				result.put("dataObj", null);
				result.put("retCount", 0);
				result.put("retMsg", "ok");
				result.put("retCode", 1);

			}
			dh.close();
			return result;
		} catch (Exception e) {
			result.put("dataObj", null);
			result.put("retCount", 0);
			result.put("retMsg", e.getMessage());
			result.put("retCode", 0);
			dh.close();
			return result;
			// TODO: handle exception
		}
	}
	/**
	 * 根据sql和参数查询记录，并返回查询结果。 返回值为：dataObj：返回查询数据 retMsg：返回信息 retCode:返回码
	 * retCount：返回记录数
	 * 
	 * @param sql
	 * @param args
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static Map queryClobList(String sql,String field, Object... args) {
		List<Object> a = new ArrayList<Object>();
		for (int i = 0; i < args.length; i++) {
			a.add(args[i]);
		}
		Map result = new HashMap();
		DbHelper dh = new DbHelper();
		try {

			ArrayList<Map> ulist = dh.queryRecords4Map(sql, a.toArray());
			
			if (ulist != null) {
				if(field!=null && field.length()>=0){
					String ret="";
					ArrayList<Map> ulist2=new ArrayList<Map>();
					for(Map o:ulist){
			    	  if (o != null) {
			    		oracle.sql.CLOB   clobtmp   =   (oracle.sql.CLOB)o.get(field);
			    	 	ret=clobtmp.getSubString((long)1,(int)clobtmp.length());
			    	 	o.put(field,ret);
			    	 	ulist2.add(o);
			    	  }
					}
					result.put("dataObj", ulist2);
				}else
				    result.put("dataObj", ulist);
				result.put("retCount", ulist.size());
				result.put("retMsg", "ok");
				result.put("retCode", 1);

			} else {
				result.put("dataObj", null);
				result.put("retCount", 0);
				result.put("retMsg", "ok");
				result.put("retCode", 1);

			}
			dh.close();
			return result;
		} catch (Exception e) {
			result.put("dataObj", null);
			result.put("retCount", 0);
			result.put("retMsg", e.getMessage());
			result.put("retCode", 0);
			dh.close();
			return result;
			// TODO: handle exception
		}
	}
	public static Map executeSqlCode(String sqlcode, Map sqlparam,
			Object... args) {
		return executeSqlCodeEx(defaultDbType, sqlcode, sqlparam, args);
	}

	@SuppressWarnings("unchecked")
	public static Map executeSqlCodeEx(String dbtype, String sqlcode,
			Map sqlparam, Object... args) {
		String sql = extGetSqlText(sqlcode, sqlparam);
		Map result = null;
		if (sql == null) {
			result.put("dataObj", "");
			result.put("retCount", 0);
			result.put("retMsg", "sql statmenet error!");
			result.put("retCode", 0);
			return result;
		}
		if (sql.trim().substring(0, 6).equalsIgnoreCase("select")) {
			return queryEx(dbtype, sql, args);
		} else {
			return executeSqlEx(dbtype, sql, args);
		}
	}

	public static Map queryPageRecord(String sql, int currentpage,
			int pagesize, Object... args) {
		return queryPageRecordEx(defaultDbType, sql, currentpage, pagesize,
				args);
	}
	public static Map queryPageRecordSqlCode(String sql,Map sqlparam, int currentpage,
			int pagesize, Object... args) {
		return queryPageRecordExSqlCode(defaultDbType, sql,sqlparam, currentpage, pagesize,
				args);
	}
	/**
	 * 根据sql和参数查询记录，并返回查询结果。 返回值为：dataObj：返回查询数据 retMsg：返回信息 retCode:返回码
	 * retCount：返回记录数
	 * 
	 * @param sql
	 * @param args
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static Map queryPageRecordEx(String dbtype, String sql,
			int currentpage, int pagesize, Object... args) {
		List<Object> a = new ArrayList<Object>();
		for (int i = 0; i < args.length; i++) {
			a.add(args[i]);
		}
		Map result = new HashMap();
		DbHelper dh = new DbHelper(dbtype);
		try {

			String tmpSql = creatPageSql(extGetSqlText(sql,null), currentpage, pagesize);

			ArrayList ulist = dh.queryRecords4Map(tmpSql, a.toArray());
			if (ulist != null) {
				result.put("dataObj", ulist);
				result.put("retCount", ulist.size());
				result.put("retMsg", "ok");
				result.put("retCode", 1);

			} else {
				result.put("dataObj", null);
				result.put("retCount", 0);
				result.put("retMsg", "ok");
				result.put("retCode", 1);

			}
			dh.close();
			return result;
		} catch (Exception e) {
			result.put("dataObj", null);
			result.put("retCount", 0);
			result.put("retMsg", e.getMessage());
			result.put("retCode", 0);

			dh.close();
			return result;
			// TODO: handle exception
		}
	}
	/**
	 * 根据sql和参数查询记录，并返回查询结果。 返回值为：dataObj：返回查询数据 retMsg：返回信息 retCode:返回码
	 * retCount：返回记录数
	 * 
	 * @param sql
	 * @param args
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static Map queryPageRecordExSqlCode(String dbtype, String sql,
			Map sqlparam,int currentpage, int pagesize, Object... args) {
		List<Object> a = new ArrayList<Object>();
		for (int i = 0; i < args.length; i++) {
			a.add(args[i]);
		}
		Map result = new HashMap();
		DbHelper dh = new DbHelper(dbtype);
		try {

			String tmpSql = creatPageSql(extGetSqlText(sql,sqlparam), currentpage, pagesize);

			ArrayList ulist = dh.queryRecords4Map(tmpSql, a.toArray());
			if (ulist != null) {
				result.put("dataObj", ulist);
				result.put("retCount", ulist.size());
				result.put("retMsg", "ok");
				result.put("retCode", 1);

			} else {
				result.put("dataObj", null);
				result.put("retCount", 0);
				result.put("retMsg", "ok");
				result.put("retCode", 1);

			}
			dh.close();
			return result;
		} catch (Exception e) {
			result.put("dataObj", null);
			result.put("retCount", 0);
			result.put("retMsg", e.getMessage());
			result.put("retCode", 0);

			dh.close();
			return result;
			// TODO: handle exception
		}
	}
	public static Map executeSql(String sql, Object... args) {
		return executeSqlEx(defaultDbType, sql, args);
	}

	/**
	 * 执行 insert、delete、update等sql语句，并返回影响的结果。 返回值为： retMsg：返回信息 retCode:返回码
	 * retCount：返回受影响的记录数
	 * 
	 * @param sql
	 * @param args
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static Map executeSqlEx(String dbtype, String sql, Object... args) {
		List<Object> a = new ArrayList<Object>();
		for (int i = 0; i < args.length; i++) {
			a.add(args[i]);
		}
		Map result = new HashMap();
		DbHelper dh = new DbHelper(dbtype);
		try {
			int i = dh.update(sql, a.toArray());
			result.put("retCount", i);
			result.put("retMsg", "ok");
			result.put("retCode", 1);
			dh.close();
			return result;
		} catch (Exception e) {

			result.put("retCount", -1);
			result.put("retMsg", e.getMessage());
			result.put("retCode", 0);
			dh.close();
			return result;
			// TODO: handle exception
		}
	}

	public static Map executeTrans(Object[] sqlTrans) {
		return executeTransEx(defaultDbType, sqlTrans);
	}

	/**
	 * 执行一个事务处理操作 'insert..',param=[]}]
	 * 
	 * @return
	 */
	public static Map executeTransEx(String dbtype, Object[] sqlTrans) {
		DbHelper dh = new DbHelper(dbtype);
		Map result = new HashMap();
		try {

			dh.beginTransaction();
			for (int i = 0; i < sqlTrans.length; i++) {
				Map sqlObj = (Map) sqlTrans[i];
				Object[] paramlist = (Object[]) sqlObj.get("param");
				Map sqlParam = (Map) sqlObj.get("sqlparam");
				String sql = sqlObj.get("sql").toString();
				if (sqlParam != null) {
					sql = extGetSqlText(sql, sqlParam);
				}
				dh.update(sql, paramlist);

			}
			dh.commit();
			result.put("retMsg", "ok");
			result.put("retCode", 1);
			return result;
		} catch (Exception e) {
			dh.rollback();
			result.put("retMsg", e.getMessage());
			result.put("retCode", 0);

			// TODO: handle exception
		}
		return result;
	}
  
    /**
     * 获取一组序列号作为唯一索引。
     * @param seqName 序列名称
     * @param iCount  获取数量
     * @return
     */
    public static ArrayList<Integer> getSeqArr(String seqName,int iCount){
    	ArrayList<Integer> result=new ArrayList<Integer>();
    	DbHelper dh=new DbHelper();
    	String sql="select "+seqName+".nextval cn from dual";
    	try{
    		for(int i=0;i<iCount;i++){
    	    	Map u=dh.query4Map(sql);
    	    	result.add(Integer.valueOf(u.get("cn").toString()));
    	    	}
    	}catch(Exception e){
    		
    	}
    	dh.close();
    	return result;
    }
 

	public static void main(String[] args) throws Exception {
		String sql="insert into businessuser.tsa(a) values(?)";

	}
}
